package com.admin.util;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.admin.model.ExcelReadResult;
import com.admin.model.FileMeta;
import com.admin.model.ImportExcelReprotModel;
import com.common.model.Department;
import com.common.model.TeacherUser;
import com.common.util.Md5Encrypt;

public class AdminTeacherExcelTool {
	//至少需要包含的表头
	static private ArrayList<String> excelHeadMinVal=null;
	public AdminTeacherExcelTool(){
		if(excelHeadMinVal==null){
			excelHeadMinVal=new ArrayList<>();
			excelHeadMinVal.add("工号");
			excelHeadMinVal.add("姓名");
			excelHeadMinVal.add("密码");
			excelHeadMinVal.add("性别");
			excelHeadMinVal.add("所属系");
		}
	}
	/**
	 * 读取excel文件
	 * @param excelFile
	 * @return 返回一个包括教师,错误信息等的bean
	 */
	public ExcelReadResult<ImportExcelReprotModel<TeacherUser>> readExcel(FileMeta excelFile){
		boolean is2003=excelFile.getFileName().endsWith(".xls");
		if(is2003){
			return readExcelOf2003(excelFile.getInputStresm());
		}else{
			return readExcelOf2007(excelFile.getInputStresm());
		}
	}
	/**
	 * 读取2003版的excel
	 * @param is
	 * @return
	 */
	public ExcelReadResult< ImportExcelReprotModel<TeacherUser>> readExcelOf2003(InputStream is){
		HSSFWorkbook wb=null;
		HSSFSheet sheet;
		ExcelReadResult<ImportExcelReprotModel<TeacherUser>> result=new ExcelReadResult<>();
		result.setTotalDataCount(0);
		try {
			wb=new HSSFWorkbook(is);
		} catch (Exception e) {
			result.setErrorMsg("文件读取错误");
			return result;
		}
		sheet=wb.getSheetAt(0);
		if(sheet==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HSSFRow excelHead=sheet.getRow(0);
		if(excelHead==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int colCount=excelHead.getLastCellNum();
		if(colCount<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HashMap<String, Integer>indexMap=new HashMap<>();
		int check=0;
		for (int i = 0; i < colCount; i++) {
			HSSFCell cell=excelHead.getCell(i);
			if(cell==null){
				continue;
			}
            String cellval=cell.getStringCellValue().toLowerCase().trim();
            if(!indexMap.containsKey(cellval)){
            	indexMap.put(cellval, i);
            	if(excelHeadMinVal.contains(cellval)){
            		check++;
            	}
            }
        }
		if(check<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int rowCount=getRowsInSheet(sheet);
		result.setTotalDataCount(rowCount-1);
		if(rowCount==1){
			result.setErrorMsg("没有任何数据");
			return result;
		}
		for(int i=1;i<rowCount;i++){
			HSSFRow row=sheet.getRow(i);
			if(row==null){
				continue;
			}
			ArrayList<String>rowData=new ArrayList<>();
			for(int j=0;j<colCount;j++){
				HSSFCell cell=row.getCell(j);
				if(cell==null){
					rowData.add("");
					continue;
				}
				try {
					rowData.add(cell.getStringCellValue());
				} catch (Exception e) {
					if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
						rowData.add((long)cell.getNumericCellValue()+"");
					}else{
						rowData.add("");
					}
				}
			}
			ImportExcelReprotModel<TeacherUser> report=getTeacherByRow(rowData, indexMap);
			if(report.getErrorMsg()!=null){
				report.setErrorLine(i+1);
			}
			result.getDataList().add(report);
		}
		return result;
	}
	public ExcelReadResult< ImportExcelReprotModel<TeacherUser>> readExcelOf2007(InputStream is){
		ExcelReadResult<ImportExcelReprotModel<TeacherUser>> result=new ExcelReadResult<>();
		result.setTotalDataCount(0);
		XSSFWorkbook wb=null;
		XSSFSheet sheet=null;
		try {
			wb=new XSSFWorkbook(is);
		} catch (Exception e) {
			result.setErrorMsg("文件读取错误");
			return result;
		}
		sheet=wb.getSheetAt(0);
		if(sheet==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		XSSFRow excelHead=sheet.getRow(0);
		if(excelHead==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int colCount=excelHead.getLastCellNum();
		if(colCount<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HashMap<String, Integer>indexMap=new HashMap<>();
		int check=0;
		for (int i = 0; i < colCount; i++) {
			XSSFCell cell=excelHead.getCell(i);
			if(cell==null){
				continue;
			}
            String cellval=cell.getStringCellValue().toLowerCase().trim();
            if(!indexMap.containsKey(cellval)){
            	indexMap.put(cellval, i);
            	if(excelHeadMinVal.contains(cellval)){
            		check++;
            	}
            }
        }
		if(check<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int rowCount=getRowsInSheet(sheet);
		result.setTotalDataCount(rowCount-1);
		if(rowCount==1){
			result.setErrorMsg("没有任何数据");
			return result;
		}
		for(int i=1;i<rowCount;i++){
			XSSFRow row=sheet.getRow(i);
			if(row==null){
				continue;
			}
			ArrayList<String>rowData=new ArrayList<>();
			for(int j=0;j<colCount;j++){
				XSSFCell cell=row.getCell(j);
				if(cell==null){
					rowData.add("");
					continue;
				}
				try {
					rowData.add(cell.getStringCellValue());
				} catch (Exception e) {
					if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						rowData.add((long)cell.getNumericCellValue()+"");
					}else{
						rowData.add("");
					}
				}
			}
			ImportExcelReprotModel<TeacherUser> report=getTeacherByRow(rowData, indexMap);
			if(report.getErrorMsg()!=null){
				report.setErrorLine(i+1);
			}
			result.getDataList().add(report);
		}
		return result;
	}
	/**
	 * 根据行数据和航索引生成包含学生及错误信息的bean
	 * @param row
	 * @param map
	 * @return
	 */
	public ImportExcelReprotModel<TeacherUser> getTeacherByRow(ArrayList<String> row,HashMap<String, Integer> map){
		ImportExcelReprotModel<TeacherUser> report=new ImportExcelReprotModel<>();
		TeacherUser teacher=new TeacherUser();
		Department dep=new Department();
		teacher.setDepartment(dep);
		report.setData(teacher);
		String no=row.get(map.get("工号"));
		if(no==null||no.trim().isEmpty()){
			report.setErrorMsg("未设置工号");
			return report;
		}
		report.getData().setNo(no.trim());
		String name=row.get(map.get("姓名"));
		if(name==null||name.trim().isEmpty()){
			report.setErrorMsg("未设置姓名");
			return report;
		}
		report.getData().setName(name.trim());
		String gender=row.get(map.get("性别"));
		if(name==null||name.trim().isEmpty()){
			report.setErrorMsg("未设置性别");
			return report;
		}
		if(gender.indexOf("男")!=-1){
			gender="男";
		}else{
			gender="女";
		}
		report.getData().setGender(gender);
		String depName=row.get(map.get("所属系"));
		if(depName==null||depName.trim().isEmpty()){
			report.setErrorMsg("未设置年级");
			return report;
		}
		report.getData().getDepartment().setName(depName.trim());
		String password=row.get(map.get("密码"));
		if(password==null||password.trim().isEmpty()){
			report.setErrorMsg("未设置密码");
			return report;
		}
		report.getData().setPassword(Md5Encrypt.encrypt(password.trim()));
		Integer qqIndex=map.get("qq");
		if(qqIndex!=null){
			String qq=row.get(qqIndex);
			if(qq!=null){
				report.getData().setQq(qq.trim());
			}
		}
		Integer telIndex=map.get("电话");
		if(telIndex!=null){
			String tel=row.get(telIndex);
			if(tel!=null){
				report.getData().setTelephone(tel.trim());
			}
		}
		Integer titleIndex=map.get("职称");
		if(titleIndex!=null){
			String title=row.get(titleIndex);
			if(title!=null){
				report.getData().setTitle(title.trim());
			}
		}	
		Integer infoIndex=map.get("其他信息");
		if(infoIndex!=null){
			String info=row.get(infoIndex);
			if(info!=null){
				report.getData().setInfo(info.trim());
			}
		}
		return report;
	}
	/**
	 * 2003版获取实际excel的行数
	 * @param sheet
	 * @return
	 */
	public int getRowsInSheet(HSSFSheet sheet){
		int maxRow=sheet.getLastRowNum();
		int actureRowCount=0;
		for(int i=0;i<maxRow+1;i++){
			HSSFRow row=sheet.getRow(i);
			int maxCol=row.getLastCellNum();
			boolean colHasData=false;
			for(int j=0;j<maxCol;j++){
				HSSFCell cell=row.getCell(j);
				if(cell==null){
					continue;
				}
				try {
					String val=cell.getStringCellValue();
					if(val==null||val.trim().isEmpty()){
						continue;
					}
				} catch (Exception e) {
				}
				colHasData=true;
				break;
			}
			if(colHasData){
				actureRowCount=i+1;
			}
		}
		return actureRowCount;
	}
	public int getRowsInSheet(XSSFSheet sheet){
		int maxRow=sheet.getLastRowNum();
		int actureRowCount=0;
		for(int i=0;i<maxRow+1;i++){
			XSSFRow row=sheet.getRow(i);
			int maxCol=row.getLastCellNum();
			boolean colHasData=false;
			for(int j=0;j<maxCol;j++){
				XSSFCell cell=row.getCell(j);
				if(cell==null){
					continue;
				}
				try {
					String val=cell.getStringCellValue();
					if(val==null||val.trim().isEmpty()){
						continue;
					}
				} catch (Exception e) {
				}
				colHasData=true;
				break;
			}
			if(colHasData){
				actureRowCount=i+1;
			}
		}
		return actureRowCount;
	}
}
